package com.etl.jdbc.jdbc;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import javax.sound.midi.Soundbank;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: Locker1995
 * Date: 2018-03-27
 * Time: 9:44 AM
 */
public class JDBCService {

    /**
     *
     *
     -----------------------------------------------------------
     DB2
     driverClass：com.ibm.db2.jcc.DB2Driver
     url：jdbc:db2://127.0.0.1:50000/dbname
     -----------------------------------------------------------
     syBase
     driverClass：com.sybase.jdbc.SybDriver
     url：jdbc:sybase:Tds:localhost:5007/dbname
     -----------------------------------------------------------
     PostgreSQL
     driverClass：org.postgresql.Driver
     url：jdbc:postgresql://localhost/dbname
     -----------------------------------------------------------
     Sql Server2000
     driverClass：com.microsoft.jdbc.sqlserver.SQLServerDriver
     url：jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbname
     -----------------------------------------------------------
     Sql Server2005
     driverClass：com.microsoft.sqlserver.jdbc.SQLServerDriver
     url：jdbc:sqlserver://localhost:1433; DatabaseName=dbname
     -----------------------------------------------------------
     */


    protected static final String DATABASE_DRIVER_MYSQL = "com.mysql.jdbc.Driver";

    protected static final String DATABASE_DRIVER_SQLSERVER = "com.microsoft.sqlserver.jdbc.SQLServerConnection";

    protected static final String DATABASE_DRIVER_ORACLE = "oracle.jdbc.driver.OracleDriver";

    private DriverManagerDataSource dataSource;

    private String url;//链接

    private String username;//用户名

    private String password;//密码

    private String driver;//数据库驱动

    private String databaseType;//数据库类型

    private String databaseName;//数据库名

    public JDBCService(String url, String username, String password, String database,String databaseName) {
        this.url = url;
        this.username = username;
        this.password = password;
        this.databaseType = database;
        this.driver = getDriverClassName(database);
        this.databaseName = databaseName;
        initDataSource();
    }

    public JDBCService(JSONObject args){
        this.username = args.getString("username");
        this.password = args.getString("password");
        this.databaseType = args.getString("dbType");
        this.databaseName = args.getString("dbSchema");
        this.url = DatabaseUrlUtil.getDatabaseUrl(this.databaseType,args.getString("dbServer"),this.databaseName);
        this.driver = getDriverClassName(this.databaseType);
        initDataSource();
    }

    /**
     * 获得table信息
     *
     * @param tableName
     */
    public List<JSONObject> findTable(String tableName) {

        List<JSONObject> columns = new ArrayList<>();
        SqlRowSet rowSet = getTableRowSet(tableName);
        SqlRowSetMetaData metaData = rowSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        for (int i = 1; i <= columnCount; i++)
            columns.add(columnJsonObject(metaData, i));

        return columns;
    }

    /**
     * 获得该表全部数据
     *
     * @param tableName
     * @return
     */
    public List<JSONObject> findAllFromTable(String tableName) {
        String sql = "select * from " + tableName;
        List<JSONObject> results = new ArrayList<>();
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
        for(Map<String,Object> map:rows){
            results.add(getJsonData(map));
        }

        return results;
    }

    /**
     *
     * 查询分页，获得分页对象
     *
     * @param tableName
     * @param pageSize
     * @param pageNumber
     * @return
     */
    public JSONObject findPage(String tableName,int pageSize,int pageNumber){

        long count = getFirstResult(tableName);

        List<JSONObject> pageData = findPageData(tableName,pageSize,pageNumber);

        JSONObject jsonObject = PageUtil.getPageObject(count,pageData,pageSize,pageNumber);

        return jsonObject;
    }

    /**
     *
     * 通常执行 类似
     * Select count(*) from tableName 这类的语句
     *
     * @param tableName
     * @return
     */
    protected long getFirstResult(String tableName){

        long number = 0;

        String countSql = PageUtil.getCountSql(this.databaseType,tableName);
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        SqlRowSet countRowSet = jdbcTemplate.queryForRowSet(countSql);

        while(countRowSet.next()){

            number = countRowSet.getInt(1);

        }

        return number;
    }

    /**
     *
     * 分页 查询
     *
     * @param tableName
     * @param pageSize
     * @param pageNumber
     * @return
     */
    public List<JSONObject> findPageData(String tableName,int pageSize,int pageNumber){

        String pageSql = PageUtil.getPageSql(this.databaseType,tableName,pageSize,pageNumber);
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        List<Map<String, Object>> page = jdbcTemplate.queryForList(pageSql);
        List<JSONObject> results = new ArrayList<>();
        for(Map<String,Object> map:page){
            results.add(getJsonData(map));
        }

        return results;
    }


    /**
     *
     * 获得对应database的 DriverClassName
     *
     * @param database
     * @return
     */
    protected String getDriverClassName(String database) {

        if ("mysql".equals(database.toLowerCase()))
            return DATABASE_DRIVER_MYSQL;
        if ("sqlserver".equals(database.toLowerCase()))
            return DATABASE_DRIVER_SQLSERVER;
        if ("oracle".equals(database.toLowerCase()))
            return DATABASE_DRIVER_ORACLE;
        return "";
    }

    /**
     * 封装column对象
     *
     * @param metaData
     * @param i
     * @return
     */
    protected JSONObject columnJsonObject(SqlRowSetMetaData metaData, int i) {

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("ColumnName", metaData.getColumnName(i));
        jsonObject.put("ColumnType", String.valueOf(metaData.getColumnType(i)));
        jsonObject.put("ColumnTypeName", metaData.getColumnTypeName(i));
        jsonObject.put("CatalogName", metaData.getCatalogName(i));
        jsonObject.put("ColumnClassName", metaData.getColumnClassName(i));
        jsonObject.put("ColumnLabel", metaData.getColumnLabel(i));
        jsonObject.put("Precision", String.valueOf(metaData.getPrecision(i)));
        jsonObject.put("Scale", String.valueOf(metaData.getScale(i)));
        jsonObject.put("SchemaName", metaData.getSchemaName(i));
        jsonObject.put("TableName", metaData.getTableName(i));
        jsonObject.put("SchemaName", metaData.getSchemaName(i));

        return jsonObject;
    }

    /**
     * 初始化对象的数据源
     */
    protected void initDataSource() {
        DriverManagerDataSource source = new DriverManagerDataSource();
        source.setDriverClassName(this.driver);
        source.setUrl(this.url);
        source.setUsername(this.username);
        source.setPassword(this.password);
        this.dataSource =source;
    }

    /**
     *
     * 识别不同的databaseType 来构造查询语句
     *
     * @param tableName
     * @return
     */
    protected SqlRowSet getTableRowSet(String tableName) {

        JdbcTemplate jdbcTemplate = getJdbcTemplate();

        String sql = "";
        SqlRowSet sqlRowSet = null;

        if ("mysql".equals(this.databaseType.toLowerCase())) {
            sql = "select * from " + tableName + "  limit 0";
            sqlRowSet= jdbcTemplate.queryForRowSet(sql);
        }
        if ("sqlserver".equals(this.databaseType.toLowerCase())) {
            sql = "select top 0 * from "+tableName+"  ";
            sqlRowSet= jdbcTemplate.queryForRowSet(sql );

        }
        if ("oracle".equals(this.databaseType.toLowerCase())) {
            sql="select * from "+tableName+" where rownum=0";
            sqlRowSet= jdbcTemplate.queryForRowSet(sql );
        }

        return sqlRowSet;
    }

    /**
     *
     * 把单条数据封装为JsonObject
     *
     * @param map
     */
    protected JSONObject getJsonData(Map<String,Object> map,boolean skipNull){
        Set<String> keys = map.keySet();
        Iterator<String> iterator=keys.iterator();
        JSONObject object = new JSONObject();
        while(iterator.hasNext()){
            String key = iterator.next();
            //跳过行列
            if("rn".equals(key))
                continue;
            Object value = map.get(key);
            if (value == null) {
                if(!skipNull)
                    object.put(key.toLowerCase(), "null");
            } else {
                object.put(key.toLowerCase(), value);
            }
        }
        return object;
    }

    protected JSONObject getJsonData(Map<String,Object> map){
        Set<String> keys = map.keySet();
        Iterator<String> iterator=keys.iterator();
        JSONObject object = new JSONObject();
        while(iterator.hasNext()){
            String key = iterator.next();
            //跳过行列
            if("rn".equals(key))
                continue;
            Object value = map.get(key);
            if (value == null) {
                    object.put(key.toLowerCase(), "null");
            } else {
                object.put(key.toLowerCase(), value);
            }
        }
        return object;
    }

    public void batchInsert(String tableName,List<JSONObject> columns,List<JSONObject> data){
        String sql=SqlUtil.getInsertSql(tableName,columns);

        int dataSize = data.size();

        if(dataSize>20000){

            int threadCount = dataSize/20000;


            for(int i = 0;i<threadCount;i++){
               BatchInsertThread batchInsertThread= new BatchInsertThread(sql,data.subList(i*20000,(i+1)*20000),columns,getJdbcTemplate(),i);
                    batchInsertThread.start();
            }

        }else{
            new BatchInsertThread(sql,data,columns,getJdbcTemplate(),0).run();
        }

    }


    protected JdbcTemplate getJdbcTemplate(){
        return new JdbcTemplate(this.dataSource);
    }

    /**
     *
     * @param tableName
     * @return
     */
    public long count(String tableName){
        return getFirstResult(tableName);
    }

    /**
     * 获得当前库的 表结构与关系
     */
    public JSONObject schemaInfo(){
        String sql = SqlUtil.getSchemaTableNamesSql(this.databaseType,this.databaseName);
//        System.out.println(sql);
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        List<Map<String, Object>> list= jdbcTemplate.queryForList(sql);
        JSONObject finalObject = new JSONObject();
        JSONObject schema = new JSONObject();
        JSONArray tables = new JSONArray();
        for(int i=0;i<list.size();i++){
            Map<String,Object> map = list.get(i);
            String tableName= (String) map.get("tableName");
            JSONObject table = new JSONObject();
            table.put("table_name",tableName);
            //获得tableName-list
            String relationSql = SqlUtil.getSchemaTableColumnsRelationSql(this.databaseType,tableName,this.databaseName);
//            System.out.println(relationSql);
            List<Map<String,Object>> list2 = jdbcTemplate.queryForList(relationSql);
            JSONArray fields = new JSONArray();
            for(int j = 0;j<list2.size();j++){
                JSONObject jsonObject =getJsonData(list2.get(j),true);
                fields.add(jsonObject);
            }
            table.put("fields",fields);
            tables.add(table);
        }
        schema.put("tables",tables);
        finalObject.put("schema",schema);
        return finalObject;
    }

}
